﻿#include <QCoreApplication>
#include "Public.h"
#include "CSqlOper.h"
#include "CGlobal.h"
#include "CDevDataCam.h"

bool CSqlOper::initSql()
{
	bool bRet=m_pDbDllOper->initSql();
	m_pDbDllOper->startWork();
	QThread::msleep(1000);
	return bRet;
}

bool CSqlOper::selectDevInfo(QString& sDevGuid, QVector<QSqlRecord>& vecRecord, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_devinfo";
	cData.sSql = "select * from pai_camer_devinfo where DEV_GUID=?";
	cData.addValue("DEV_GUID", sDevGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
}

bool CSqlOper::insertDevInfo(QString& sDevGuid, QString& sDevName, QString& sCamerDevGuid, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_devinfo";
	cData.sSql = "insert into pai_camer_devinfo (DEV_GUID,DEV_NAME,CAMER_DEV_GUID)"
		" values(?,?,?)";

	cData.addValue("DEV_GUID", sDevGuid);
	cData.addValue("DEV_NAME", sDevName);
	cData.addValue("CAMER_DEV_GUID", sCamerDevGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::deleteDevInfo(QString sDevId, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_devinfo";
	cData.sSql = "delete from pai_camer_devinfo where DEV_GUID =?";

	cData.addValue("DEV_GUID", sDevId);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::selectCamerInfo(QString sDevGuid, QVector<QSqlRecord>& vecRecord, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_config";
	cData.sSql = "select * from pai_camer_config where DEV_GUID=?";
	cData.addValue("DEV_GUID", sDevGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
}

bool CSqlOper::updateCamerInfo(QString sDevGuid, QString sCamerName, QString sCamerIp, int nPort, QString sCamerUser, QString sCamerPasswd, QString sRecordPath, QString sRtspUrl,
	QString sAlgType,int nChannel, QString sServerIp,int nVideoType,int nChannelHW,QString sRtspHW, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_config";
	cData.sSql = "update pai_camer_config set DEV_NAME=?,CAMER_IP=?,CAMER_PORT=?,CAMER_USER=?,CAMER_PASSWD=?,RECORD_PATH=?,RTSP_URL=?,ALG_TYPE=?,CHANNEL=?,SERVER_IP=?,CAMER_TYPE=?,CHANNEL_HW=?,RTSP_HW_URL=? where DEV_GUID=?";
	cData.addValue("DEV_NAME", sCamerName);
	cData.addValue("CAMER_IP", sCamerIp);
	cData.addValue("CAMER_PORT", nPort);
	cData.addValue("CAMER_USER", sCamerUser);
	cData.addValue("CAMER_PASSWD", sCamerPasswd);
	cData.addValue("RECORD_PATH", sRecordPath);
	cData.addValue("RTSP_URL", sRtspUrl);
	cData.addValue("ALG_TYPE", sAlgType);
	cData.addValue("CHANNEL", nChannel);
	cData.addValue("SERVER_IP", sServerIp);
	cData.addValue("CAMER_TYPE", nVideoType);
	cData.addValue("CHANNEL_HW", nChannelHW);
	cData.addValue("RTSP_HW_URL", sRtspHW);
	cData.addValue("DEV_GUID", sDevGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::insertCamerInfo(QString sDevGuid, QString sCamerName, QString sCamerIp, int nPort, QString sCamerUser, QString sCamerPasswd, QString sRecordPath, QString sRtspUrl, 
	QString sAlgType, int nChannel, QString sServerIp, int nVideoType, int nChannelHW, QString sRtspHW, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_config";
	cData.sSql = "insert into pai_camer_config (DEV_GUID,DEV_NAME,CAMER_IP,CAMER_PORT,CAMER_USER,CAMER_PASSWD,CHANNEL,RECORD_PATH,RTSP_URL,ALG_TYPE,SERVER_IP,CAMER_TYPE,CHANNEL_HW,RTSP_HW_URL)"
		" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	cData.addValue("DEV_GUID", sDevGuid);
	cData.addValue("DEV_NAME", sCamerName);
	cData.addValue("CAMER_IP", sCamerIp);
	cData.addValue("CAMER_PORT", nPort);
	cData.addValue("CAMER_USER", sCamerUser);
	cData.addValue("CAMER_PASSWD", sCamerPasswd);
	cData.addValue("CHANNEL", nChannel);
	cData.addValue("RECORD_PATH", sRecordPath);
	cData.addValue("RTSP_URL", sRtspUrl);
	cData.addValue("ALG_TYPE", sAlgType);
	cData.addValue("SERVER_IP", sServerIp);
	cData.addValue("CAMER_TYPE", nVideoType);
	cData.addValue("CHANNEL_HW", nChannelHW);
	cData.addValue("RTSP_HW_URL", sRtspHW);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::deleteCamerInfo(QString sDevGuid, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_config";
	cData.sSql = "delete from pai_camer_config where DEV_GUID =?";

	cData.addValue("DEV_GUID", sDevGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::insertAlarmRecord(QString sGuid, QString sDevGuid, QString sCamerName, QString sCamerIp, int nChannel, QDateTime dAlarmTime,
	QString sEventType, QString sEventState, QString sImgUrl,QString sCamerUser,QString sCamerPassWd,QString sAlarmDesc, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_alarm_record";
	cData.sSql = "insert into pai_alarm_record (GUID,DEV_GUID,DEV_NAME,CAMER_IP,CHANNEL,ALARM_TIME,EVENT_TYPE,EVENT_STATE,IMG_URL,CAMER_USER,CAMER_PASSWD,ALARM_DESC)"
		" values(?,?,?,?,?,?,?,?,?,?,?,?)";

	cData.addValue("GUID", sGuid);
	cData.addValue("DEV_GUID", sDevGuid);
	cData.addValue("DEV_NAME", sCamerName);
	cData.addValue("CAMER_IP", sCamerIp);
	cData.addValue("CHANNEL", nChannel);
	cData.addValue("ALARM_TIME", dAlarmTime);
	cData.addValue("EVENT_TYPE", sEventType);
	cData.addValue("EVENT_STATE", sEventState);
	cData.addValue("IMG_URL", sImgUrl);
	cData.addValue("CAMER_USER", sCamerUser);
	cData.addValue("CAMER_PASSWD", sCamerPassWd);
	cData.addValue("ALARM_DESC", sAlarmDesc);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::selectQuertAlarmRecord(QString sDevGuid, QVector<QSqlRecord>& vecRecord, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_alarm_record";
	cData.sSql = "select * from pai_alarm_record where DEV_GUID=? ORDER BY ALARM_TIME DESC LIMIT 50";
	cData.addValue("DEV_GUID", sDevGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
}

bool CSqlOper::selectQuertAlarmRecordByGuid(QString sGuid, QVector<QSqlRecord>& vecRecord, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_alarm_record";
	cData.sSql = "select * from pai_alarm_record where GUID=?";
	cData.addValue("GUID", sGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
}

bool CSqlOper::updateAlarmRecord(QString sGuid, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_alarm_record";
	cData.sSql = "update pai_alarm_record set IS_CONFIRM=1 where GUID=?";
	cData.addValue("GUID", sGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::selectCamerName(QVector<QSqlRecord>& vecRecord, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_config";
	cData.sSql = "select * from pai_camer_config";
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
}

bool CSqlOper::selectAlarmResult(QString sDevGuid, QString sAlarmType, QString sStartTime, QString sEndTime, QVector<QSqlRecord>& vecRecord, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_alarm_record";
	cData.sSql = QString("select * from pai_alarm_record where ALARM_TIME >= str_to_date('%1','%Y/%m/%d %T') and ALARM_TIME <= str_to_date('%2','%Y/%m/%d %T') ")
		.arg(sStartTime)
		.arg(sEndTime);
	if (sDevGuid != "*")
	{
		cData.sSql += QString(" and DEV_GUID='%1' ").arg(sDevGuid);
	}
	if (sAlarmType != "*")
	{
		cData.sSql += QString(" and EVENT_TYPE='%1' ").arg(sAlarmType);
	}
	cData.sSql += "order by ALARM_TIME desc";
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
	
}

bool CSqlOper::selectAlarmResult(QString sStartTime, QString sEndTime, QVector<QSqlRecord>& vecRecord, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_alarm_record";
	cData.sSql = QString("select * from pai_alarm_record where ALARM_TIME >= str_to_date('%1','%Y/%m/%d %T') and ALARM_TIME <= str_to_date('%2','%Y/%m/%d %T') ")
		.arg(sStartTime)
		.arg(sEndTime);
	cData.sSql += "order by ALARM_TIME desc";
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
}

bool CSqlOper::insertDefDevCfg(QString sDevGuid, QString sDevName, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_config";
	cData.sSql = "insert into pai_camer_config (DEV_GUID,DEV_NAME,CAMER_IP,CAMER_PORT,CAMER_USER,CAMER_PASSWD,RECORD_PATH,RTSP_URL)"
		" values(?,?,?,?,?,?,?,?)";

	cData.addValue("DEV_GUID", sDevGuid);
	cData.addValue("DEV_NAME", sDevName);
	cData.addValue("CAMER_IP", "127.0.0.1");
	cData.addValue("CAMER_PORT",8000);
	cData.addValue("CAMER_USER", "admin");
	cData.addValue("CAMER_PASSWD", "ky002380");
	cData.addValue("RECORD_PATH", "");
	cData.addValue("RTSP_URL", "");
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::selectCamerInfo(QString sDevGuid, CDevDataCam* pDevData, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_config";
	cData.sSql = "select * from pai_camer_config where DEV_GUID=?";
	cData.addValue("DEV_GUID", sDevGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	QVector<QSqlRecord> vecDataInfo = cData.vecRecord;
	for (int i = 0; i < vecDataInfo.size(); i++)
	{
		pDevData->m_sCamerName = vecDataInfo[i].value("DEV_NAME").toString().trimmed();
		pDevData->m_sCamerIp = vecDataInfo[i].value("CAMER_IP").toString().trimmed();
		pDevData->m_nCamerPort = vecDataInfo[i].value("CAMER_PORT").toInt();
		pDevData->m_nVideoType = vecDataInfo[i].value("CAMER_TYPE").toInt();
		pDevData->m_sServerIp = vecDataInfo[i].value("SERVER_IP").toString().trimmed();
		pDevData->m_nChannel = vecDataInfo[i].value("CHANNEL").toInt();
		pDevData->m_nChannelHW = vecDataInfo[i].value("CHANNEL_HW").toInt();
		pDevData->m_sUserName = vecDataInfo[i].value("CAMER_USER").toString().trimmed();
		pDevData->m_sPassWord = vecDataInfo[i].value("CAMER_PASSWD").toString().trimmed();
		pDevData->m_sRecordPath = vecDataInfo[i].value("RECORD_PATH").toString().trimmed();
		pDevData->m_sRtspUrlImg = vecDataInfo[i].value("RTSP_URL").toString().trimmed();
		pDevData->m_sRtspUrlIr = vecDataInfo[i].value("RTSP_HW_URL").toString().trimmed();
		QString sAlgType=vecDataInfo[i].value("ALG_TYPE").toString().trimmed();
		QStringList slistAlgType = sAlgType.split(",");
		for (int i = 0; i < slistAlgType.size(); i++)
		{
			if (slistAlgType.at(i) != "")
			{
				pDevData->m_vecAlgType.push_back(slistAlgType.at(i).toInt());
			}
		}
	}
	return bRet;
}

bool CSqlOper::selectTagID(QString& sDevGuid, QString& sTagID, QVector<QSqlRecord>& vecRecord, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "tag_config";
	cData.sSql = "select * from tag_config where DEV_GUID=? and TAG_ID=?";
	cData.addValue("DEV_GUID", sDevGuid);
	cData.addValue("TAG_ID", sTagID);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
}

bool CSqlOper::insertTagID(QString& sDevGuid, QString& sTagID, QString& sTagAddr, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "tag_config";
	cData.sSql = "insert into tag_config (DEV_GUID,TAG_ID,TAG_ADDR)"
		" values(?,?,?)";

	cData.addValue("DEV_GUID", sDevGuid);
	cData.addValue("TAG_ID", sTagID);
	cData.addValue("TAG_ADDR", sTagAddr);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::updateTagID(QString& sDevGuid, QString& sTagID, QString& sTagAddr, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "tag_config";
	cData.sSql = "update tag_config set TAG_ADDR = ? where DEV_GUID = ? and TAG_ID = ?";

	cData.addValue("TAG_ADDR", sTagAddr);
	cData.addValue("DEV_GUID", sDevGuid);
	cData.addValue("TAG_ID", sTagID);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::selectResultLastTime(QVector<QSqlRecord>& vecRecord, QString sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "result_record";
	cData.sSql = "SELECT COALESCE(MAX(RESULT_TIME),NOW()) AS latest_time FROM result_record where RESULT_TYPE = 1;";
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecRecord = cData.vecRecord;
	return bRet;
}

bool CSqlOper::insertResult(QString sGuid, int nResultType, QString sResultName, QDateTime dTime, QString sPath, QString sHttpUrl, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "result_record";
	cData.sSql = "insert into result_record (GUID,RESULT_TYPE,RESULT_NAME,RESULT_TIME,RESULT_PATH,RESULT_PATH_URL)"
		" values(?,?,?,?,?,?)";

	cData.addValue("GUID", sGuid);
	cData.addValue("RESULT_TYPE", nResultType);
	cData.addValue("RESULT_NAME", sResultName);
	cData.addValue("RESULT_TIME", dTime);
	cData.addValue("RESULT_PATH", sPath);
	cData.addValue("RESULT_PATH_URL", sHttpUrl);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::selectServerChannel(QString sServerIp, int nChannel, QVector<QSqlRecord>& vecInfo, QString sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "pai_camer_devinfo";
	cData.sSql = "SELECT * FROM pai_camer_devinfo where SERVER_IP = ? and CHANNEL=?";
	cData.addValue("SERVER_IP", sServerIp);
	cData.addValue("CHANNEL", nChannel);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecInfo = cData.vecRecord;
	return bRet;
}

bool CSqlOper::selectResult(QString sStartTime, QString sEndTime, QVector<QSqlRecord>& vecInfo, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "result_record";
	cData.sSql = QString("select * from result_record where RESULT_TIME >= str_to_date('%1','%Y/%m/%d %T') and RESULT_TIME <= str_to_date('%2','%Y/%m/%d %T') and RESULT_TYPE=1 ")
		.arg(sStartTime)
		.arg(sEndTime);
	cData.sSql += "order by RESULT_TIME desc";
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	vecInfo = cData.vecRecord;
	return bRet;
}

bool CSqlOper::insertPubCardCfg(QString sCardGuid, int nCardType, QString sDevList, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "ppub_card_cfg";
	cData.sSql = "insert into ppub_card_cfg (CARD_GUID,CARD_TYPE,DEV_LIST)"
		" values(?,?,?)";

	cData.addValue("CARD_GUID", sCardGuid);
	cData.addValue("CARD_TYPE", nCardType);
	cData.addValue("DEV_LIST", sDevList);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::selectPubCardCfg(QString sCardGuid, QVector<QSqlRecord>& vecInfo, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "ppub_card_cfg";
	cData.sSql = "select * from ppub_card_cfg where CARD_GUID=?";
	cData.addValue("CARD_GUID", sCardGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	vecInfo = cData.vecRecord;
	sErrMsg = cData.sErr;
	return bRet;
}

bool CSqlOper::updatePubCardCfg(QString sCarGuid, QString sDevList, QString& sErrMsg)
{
	CDatabaseExecData cData;
	cData.sTableName = "ppub_card_cfg";
	cData.sSql = "update ppub_card_cfg set DEV_LIST = ? where CARD_GUID = ?";

	cData.addValue("DEV_LIST", sDevList);
	cData.addValue("CARD_GUID", sCarGuid);
	bool bRet = m_pDbDllOper->exec(cData);
	sErrMsg = cData.sErr;
	return bRet;
}

CSqlOper::CSqlOper()
{
	//配置文件地址
	QString sFilePath = QCoreApplication::applicationDirPath() + "/DLL/HKAICfg/HKAICfg.xml";
	QString sDllPath = QCoreApplication::applicationDirPath() + "/SqlOper.dll";
	m_pDbDllOper = new CSqlDllOper(g_sTableName, sFilePath, sDllPath);
}

CSqlOper::~CSqlOper()
{
	delete m_pDbDllOper;
	m_pDbDllOper = nullptr;
}
